package com.zhibi.util.poi;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.zhibi.util.reflect.ReflectUtil;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * poi 读取excel 支持2003 --2007 及以上文件
 */
public class ExcelUtil {

	public static final String UID="serialVersionUID"; 

	public static enum Type{
		XLS,XLSX
	}

	/**
	 * 合并方法，读取excel文件
	 * 根据文件名自动识别读取方式
	 * 支持97-2013格式的excel文档
	 * 
	 * @return 返回列表内容格式：
	 *  每一行数据都是以对应列的表头为key 内容为value 比如 excel表格为：
	 * ===============
	 *  A | B | C | D
	 * ===|===|===|===
	 *  1 | 2 | 3 | 4
	 * ---|---|---|--- 
	 *  a | b | c | d
	 * ---------------
	 * 返回值 map：
	 *   map1:   A:1 B:2 C:3 D:4
	 *   map2:   A:a B:b C:d D:d
	 * @throws java.io.IOException
	 */
	@SuppressWarnings("rawtypes")
	public static List<Map> readExcel(String filePath) throws Exception{
		if(getExtensionName(filePath).equalsIgnoreCase("xls")){
			return readExcel2003(filePath);
		}else if(getExtensionName(filePath).equalsIgnoreCase("xlsx")) {
			return readExcel2007(filePath);
		}else{
			throw new Exception("要读取的不是excel文件");
		}
	}

	@SuppressWarnings("rawtypes")
	public static List<Map> readExcel(File file,Type type) throws Exception{
		if(type ==Type.XLS){
			return readExcel2003(file);
		}else if(type ==Type.XLSX) {
			return readExcel2007(file);
		}else{
			throw new Exception("要读取的不是excel文件");
		}
	}

	@SuppressWarnings("rawtypes")
	public static List<Map> readExcel(FileInputStream fis,Type type) throws Exception{
		if(type ==Type.XLS){
			return readExcel2003(fis);
		}else if(type ==Type.XLSX) {
			return readExcel2007(fis);
		}else{
			throw new Exception("要读取的不是excel文件");
		}
	}

	@SuppressWarnings("rawtypes")
	private static List<Map> readExcel2003(File file) throws IOException{
		return readExcel2003(new FileInputStream(file));
	}

	@SuppressWarnings({ "rawtypes", "resource" })
	private static List<Map> readExcel2003(FileInputStream fis) throws IOException{
		List<Map> valueList=new ArrayList<Map>();
		try {
			HSSFWorkbook wookbook = new HSSFWorkbook(fis);	// 创建对Excel工作簿文件的引用
			HSSFSheet sheet = wookbook.getSheetAt(0);	// 在Excel文档中，第一张工作表的缺省索引是0
			int rows = sheet.getPhysicalNumberOfRows();	// 获取到Excel文件中的所有行数­
			Map<Integer,String> keys=new HashMap<Integer, String>();
			int cells=0;
			// 遍历行­（第1行  表头） 准备Map里的key
			HSSFRow firstRow = sheet.getRow(0);
			if (firstRow != null) {
				// 获取到Excel文件中的所有的列
				cells = firstRow.getPhysicalNumberOfCells();
				// 遍历列
				for (int j = 0; j < cells; j++) {
					// 获取到列的值­
					try {
						HSSFCell cell = firstRow.getCell(j);
						String cellValue = getCellValue(cell);
						keys.put(j,cellValue);						
					} catch (Exception e) {
						e.printStackTrace();	
					}
				}
			}
			// 遍历行­（从第二行开始）
			for (int i = 1; i < rows; i++) {
				// 读取左上端单元格(从第二行开始)
				HSSFRow row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					//准备当前行 所储存值的map
					Map<String, Object> val=new HashMap<String, Object>();

					boolean isValidRow = false;

					// 遍历列
					for (int j = 0; j < cells; j++) {
						// 获取到列的值­
						try {
							HSSFCell cell = row.getCell(j);
							String cellValue = getCellValue(cell);
							val.put(keys.get(j),cellValue);	
							if(!isValidRow && cellValue!=null && cellValue.trim().length()>0){
								isValidRow = true;
							}
						} catch (Exception e) {
							e.printStackTrace();		
						}
					}
					//第I行所有的列数据读取完毕，放入valuelist
					if(isValidRow){
						valueList.add(val);
					}
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			fis.close();
		}
		return valueList;
	}

	/**
	 * 读取97-2003格式
	 * @param filePath 文件路径
	 * @throws java.io.IOException
	 */
	@SuppressWarnings("rawtypes")
	private static List<Map> readExcel2003(String filePath) throws IOException{
		return readExcel2003(new FileInputStream(filePath));
	}

	@SuppressWarnings("rawtypes")
	private static List<Map> readExcel2007(File file) throws IOException{
		return readExcel2007(new FileInputStream(file));
	}

	@SuppressWarnings({ "rawtypes", "resource" })
	private static List<Map> readExcel2007(FileInputStream fis) throws IOException{
		List<Map> valueList=new ArrayList<Map>();
		try {
			XSSFWorkbook xwb = new XSSFWorkbook(fis);	// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFSheet sheet = xwb.getSheetAt(0);			// 读取第一章表格内容
			// 定义 row、cell
			XSSFRow row;
			// 循环输出表格中的第一行内容   表头
			Map<Integer, String> keys=new HashMap<Integer, String>();
			row = sheet.getRow(0);
			if(row !=null){
				//System.out.println("j = row.getFirstCellNum()::"+row.getFirstCellNum());
				//System.out.println("row.getPhysicalNumberOfCells()::"+row.getPhysicalNumberOfCells());
				for (int j = row.getFirstCellNum(); j <=row.getPhysicalNumberOfCells(); j++) {
					// 通过 row.getCell(j).toString() 获取单元格内容，
					if(row.getCell(j)!=null){
						if(!row.getCell(j).toString().isEmpty()){
							keys.put(j, row.getCell(j).toString());
						}
					}else{
						keys.put(j, "K-R1C"+j+"E");
					}
				}
			}
			// 循环输出表格中的从第二行开始内容
			for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				if (row != null) {
					boolean isValidRow = false;
					Map<String, Object> val = new HashMap<String, Object>();
					for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
						XSSFCell cell = row.getCell(j);
						if (cell != null) {
							String cellValue = null;
							if(cell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
								if(DateUtil.isCellDateFormatted(cell)){
									cellValue = new DataFormatter().formatRawCellContents(cell.getNumericCellValue(), 0, "yyyy-MM-dd HH:mm:ss");
								}
								else{
									cellValue = String.valueOf(cell.getNumericCellValue());
								}
							}
							else{
								cellValue = cell.toString();
							}
							if(cellValue!=null&&cellValue.trim().length()<=0){
								cellValue=null;
							}
							val.put(keys.get(j), cellValue);
							if(!isValidRow && cellValue!= null && cellValue.trim().length()>0){
								isValidRow = true;
							}
						}
					}

					// 第I行所有的列数据读取完毕，放入valuelist
					if (isValidRow) {
						valueList.add(val);
					}
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			fis.close();
		}
		return valueList;
	}

	/**
	 * 读取2007-2013格式
	 * @param filePath 文件路径
	 * @return
	 * @throws java.io.IOException
	 */
	@SuppressWarnings("rawtypes")
	private static List<Map> readExcel2007(String filePath) throws IOException{
		return readExcel2007(new FileInputStream(filePath));
	}

	/**
	 * 文件操作 获取文件扩展名
	 * 
	 * @Author: sunny
	 * @param filename
	 *            文件名称包含扩展名
	 * @return
	 */
	public static String getExtensionName(String filename) {
		if ((filename != null) && (filename.length() > 0)) {
			int dot = filename.lastIndexOf('.');
			if ((dot > -1) && (dot < (filename.length() - 1))) {
				return filename.substring(dot + 1);
			}
		}
		return filename;
	}

	private static String getCellValue(HSSFCell cell) {
		DecimalFormat df = new DecimalFormat("#");
		String cellValue=null;
		if (cell == null)
			return null;
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			if(HSSFDateUtil.isCellDateFormatted(cell)){
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				cellValue=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
				break;
			}
			cellValue=df.format(cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_STRING:			
			cellValue=String.valueOf(cell.getStringCellValue());
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			cellValue=String.valueOf(cell.getCellFormula());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			cellValue=null;
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			cellValue=String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_ERROR:
			cellValue=String.valueOf(cell.getErrorCellValue());
			break;
		}
		if(cellValue!=null&&cellValue.trim().length()<=0){
			cellValue=null;
		}
		return cellValue;
	}

	public static void writeExcel(OutputStream os,String[] titles,List<?> list,String[] fields,Class<?> clazz) throws Exception{
		//工作区     
		XSSFWorkbook wb = new XSSFWorkbook();  
		//创建第一个sheet     
		XSSFSheet sheet= wb.createSheet("sheet1");   
		int start = 0;
		//生成表头
		if(titles!=null && titles.length>0){
			XSSFRow row = sheet.createRow(0);
			for(int i=0;i<titles.length;i++){
				row.createCell(i).setCellValue(titles[i]); 
			}
			start++;
		}
		//主要内容
		for(int i=start;i<list.size()+start;i++){
			Object o=list.get(i-start);
			if(o==null){
				continue;
			}
			XSSFRow row = sheet.createRow(i);
			String value = null;
			for(int j=0;j<fields.length;j++){
				value=ReflectUtil.invokeGetMethod(clazz, o, fields[j]).toString();
				row.createCell(j).setCellValue(value);
			}
		}
		wb.write(os);
		wb.close();
	}
}
